<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use app\index\controller\Login;
use think\Db;
use app\index\service\ExcelService;

class LandTable extends BaseTable
{
    protected static $view = 'V_ASSET_LAND';
    protected static $table = 'ASSET_LAND';
    protected static $pk = 'LAND_NO';
    protected static $configTable = 'ASSET_LAND_CONFIG';

    static public function stable(){
        return Db::table(static::$table);
    }

    static public function table(){
        $sql = "(
            select 
            l.LAND_NO
            ,to_char(l.input_date,'yyyy-mm-dd') input_date
            ,l.CHECK_LAND_NO
            ,l.ASSET_TYPE_ID
            ,d1.cname94 ASSET_TYPE
            ,d1.ccode2010
            ,d1.cname2010
            ,l.LAND_NAME
            ,l.LAND_UNIT
            ,l.LAND_SIZE
            ,l.LAND_CERT_SIZE
            ,l.OWNER_CERT
            ,l.OWNER_CERT_NO
            ,l.PROPERTYRIGHT_ID
            ,d2.PROPERTYRIGHT
            ,l.LAND_MARK_NO
            ,l.POWERTYPE_ID
            ,d3.POWERTYPE
            ,to_char(l.CERT_GET_DATE,'yyyy-mm-dd') CERT_GET_DATE
            ,l.ACCOUNT_CERT_NO
            ,l.LAND_AREA
            ,l.VALUETYPE_ID
            ,d4.VALUETYPE
            ,l.LAND_FEE
            ,l.AVG_FEE
            ,l.FINANCE_GET_FEE
            ,l.BUSINESS_GET_FEE
            ,l.BUSINESS_BUDGET_GET_FEE
            ,l.OTHER_FEE
            ,l.OTHER_FINANCE_FEE
            ,to_char(l.GET_DATE,'yyyy-mm-dd') GET_DATE
            ,l.GETTYPE_ID
            ,d5.GETTYPE
            ,l.SELF_SIZE
            ,l.SELF_FEE
            ,l.BORROW_SIZE
            ,l.BORROW_FEE
            ,l.RENTAL_SIZE
            ,l.RENTAL_FEE
            ,l.INVESTMENT_SIZE
            ,l.INVESTMENT_FEE
            ,l.GUARANTEE_SIZE
            ,l.GUARANTEE_FEE
            ,l.ELSE_SIZE
            ,l.ELSE_FEE
            ,l.PURCHASE_ID
            ,d6.PURCHASE
            ,l.STATUS_ID
            ,d7.STATUS
            ,l.BORROW_DEPART
            ,l.RENTAL_DEPART
            ,l.COLLEGE_ID
            ,d8.COLLEGE
            ,l.REMARK
            ,l.USER_ID
            ,d9.NAME USER_NAME
            ,to_char(l.OPER_DATETIME,'yyyy-mm-dd') OPER_DATETIME
            from app_asset.asset_land l
            left join app_asset.ASSET_DICT_TYPE94TO2010 d1 on d1.ccode94=l.asset_type_id
            left join app_asset.ASSET_DICT_PROPERTYRIGHT d2 on d2.PROPERTYRIGHT_ID=l.PROPERTYRIGHT_ID
            left join app_asset.ASSET_DICT_POWERTYPE d3 on d3.POWERTYPE_ID=l.POWERTYPE_ID
            left join app_asset.ASSET_DICT_VALUETYPE d4 on d4.valuetype_id=l.VALUETYPE_ID
            left join app_asset.ASSET_DICT_GETTYPE d5 on d5.GETTYPE_ID=l.GETTYPE_ID
            left join app_asset.ASSET_DICT_PURCHASE d6 on d6.PURCHASE_ID=l.PURCHASE_ID
            left join app_asset.ASSET_DICT_STATUS d7 on d7.STATUS_ID=l.STATUS_ID
            left join app_asset.DICT_COLLEGE d8 on d8.COLLEGE_ID=l.COLLEGE_ID
            left join app_asset.EMPLOYEE d9 on d9.STAFF_ID=l.USER_ID
        )";
        return Db::table($sql);
    }

    static public function add($data){
        $data[self::$pk] = static::getNextId();
        $data['USER_ID'] = Login::getUserId();
        $data['INPUT_DATE'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'INPUT_DATE');
        Db::table(static::$table)->insert($data);
        return $data[self::$pk];
    }

    static public function addImport($data){
        $data['USER_ID'] = Login::getUserId();
        $data['INPUT_DATE'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'INPUT_DATE');
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        self::toDate($data,'GET_DATE');
        self::toDate($data,'CERT_GET_DATE');
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getConfigAdd(){
        return Db::table(static::$configTable)->where(['ADD_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigEdit(){
        return Db::table(static::$configTable)->where(['EDIT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigPrint(){
        return Db::table(static::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.static::$pk.',5,4)) as NUM')
            ->where([['SUBSTR('.static::$pk.',1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%04d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'土地编号','field'=>'LAND_NO'],
            ['title'=>'清查资产编号','field'=>'CHECK_LAND_NO'],
            ['title'=>'资产分类代码94','field'=>'ASSET_TYPE_ID'],
            ['title'=>'资产分类94','field'=>'ASSET_TYPE'],
            ['title'=>'资产分类代码2010','field'=>'CCODE2010'],
            ['title'=>'资产分类2010','field'=>'CNAME2010'],
            ['title'=>'资产名称','field'=>'LAND_NAME'],
            ['title'=>'计量单位','field'=>'LAND_UNIT'],
            ['title'=>'土地面积','field'=>'LAND_SIZE'],
            ['title'=>'证载明面积','field'=>'LAND_CERT_SIZE'],
            ['title'=>'权属证明','field'=>'OWNER_CERT'],
            ['title'=>'权属证号','field'=>'OWNER_CERT_NO'],
            ['title'=>'产权形式ID','field'=>'PROPERTYRIGHT_ID'],
            ['title'=>'产权形式','field'=>'PROPERTYRIGHT'],
            ['title'=>'地号','field'=>'LAND_MARK_NO'],
            ['title'=>'权属性质ID','field'=>'POWERTYPE_ID'],
            ['title'=>'权属性质','field'=>'POWERTYPE'],
            ['title'=>'发证时间','field'=>'CERT_GET_DATE'],
            ['title'=>'会计凭证号','field'=>'ACCOUNT_CERT_NO'],
            ['title'=>'坐落位置','field'=>'LAND_AREA'],
            ['title'=>'价值类型ID','field'=>'VALUETYPE_ID'],
            ['title'=>'价值类型','field'=>'VALUETYPE'],
            ['title'=>'价值','field'=>'LAND_FEE'],
            ['title'=>'均价','field'=>'AVG_FEE'],
            ['title'=>'财政拨款','field'=>'FINANCE_GET_FEE'],
            ['title'=>'事业收入','field'=>'BUSINESS_GET_FEE'],
            ['title'=>'事业收入：预算外收入','field'=>'BUSINESS_BUDGET_GET_FEE'],
            ['title'=>'其他资金','field'=>'OTHER_FEE'],
            ['title'=>'其他资金：财政性结余资金','field'=>'OTHER_FINANCE_FEE'],
            ['title'=>'取得日期','field'=>'GET_DATE'],
            ['title'=>'取得方式ID','field'=>'GETTYPE_ID'],
            ['title'=>'取得方式','field'=>'GETTYPE'],
            ['title'=>'自用面积','field'=>'SELF_SIZE'],
            ['title'=>'自用价值','field'=>'SELF_FEE'],
            ['title'=>'出借面积','field'=>'BORROW_SIZE'],
            ['title'=>'出借价值','field'=>'BORROW_FEE'],
            ['title'=>'出租面积','field'=>'RENTAL_SIZE'],
            ['title'=>'出租价值','field'=>'RENTAL_FEE'],
            ['title'=>'对外投资面积','field'=>'INVESTMENT_SIZE'],
            ['title'=>'对外投资价值','field'=>'INVESTMENT_FEE'],
            ['title'=>'担保面积','field'=>'GUARANTEE_SIZE'],
            ['title'=>'担保价值','field'=>'GUARANTEE_FEE'],
            ['title'=>'其他面积','field'=>'ELSE_SIZE'],
            ['title'=>'其他价值','field'=>'ELSE_FEE'],
            ['title'=>'采购组织形式ID','field'=>'PURCHASE_ID'],
            ['title'=>'采购组织形式','field'=>'PURCHASE'],
            ['title'=>'使用状况ID','field'=>'STATUS_ID'],
            ['title'=>'使用状况','field'=>'STATUS'],
            ['title'=>'出借单位','field'=>'BORROW_DEPART'],
            ['title'=>'出租单位','field'=>'RENTAL_DEPART'],
            ['title'=>'使用\管理部门ID','field'=>'COLLEGE_ID'],
            ['title'=>'使用\管理部门','field'=>'COLLEGE'],
            ['title'=>'备注','field'=>'REMARK'],
            ['title'=>'制单人','field'=>'USER_NAME'],
            ['title'=>'单据日期','field'=>'INPUT_DATE'],
            ['title'=>'操作时间','field'=>'OPER_DATETIME'],
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const LAND_FIELD_LIST = [
        "LAND_NO","CHECK_LAND_NO","ASSET_TYPE_ID","LAND_NAME","LAND_UNIT","LAND_SIZE","LAND_CERT_SIZE","OWNER_CERT","OWNER_CERT_NO","PROPERTYRIGHT_ID","LAND_MARK_NO","POWERTYPE_ID","CERT_GET_DATE","ACCOUNT_CERT_NO","LAND_AREA","VALUETYPE_ID","LAND_FEE","AVG_FEE","FINANCE_GET_FEE","BUSINESS_GET_FEE","BUSINESS_BUDGET_GET_FEE","OTHER_FEE","OTHER_FINANCE_FEE","GET_DATE","GETTYPE_ID","SELF_SIZE","SELF_FEE","BORROW_SIZE","BORROW_FEE","RENTAL_SIZE","RENTAL_FEE","INVESTMENT_SIZE","INVESTMENT_FEE","GUARANTEE_SIZE","GUARANTEE_FEE","ELSE_SIZE","ELSE_FEE","PURCHASE_ID","STATUS_ID","BORROW_DEPART","RENTAL_DEPART","COLLEGE_ID","REMARK",
    ];
    public static function getSessionConditions() {
        $condition = null;
        $listCondition = Login::getListConditionLand();
        if (!empty($listCondition['LAND_NO'])) {
            $value = $listCondition['LAND_NO'];
            $condition[] = ['LAND_NO','like',"$value%"];
        }
        if (!empty($listCondition['LAND_NAME'])) {
            $value = $listCondition['LAND_NAME'];
            $condition[] = ['LAND_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['LAND_MARK_NO'])) {
            $value = $listCondition['LAND_MARK_NO'];
            $condition[] = ['LAND_MARK_NO','like',"%$value%"];
        }
        if (!empty($listCondition['ASSET_TYPE_ID'])) {
            $value = $listCondition['ASSET_TYPE_ID'];
            $condition[] = ['ASSET_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['COLLEGE_ID'])) {
            $value = $listCondition['COLLEGE_ID'];
            $condition[] = ['COLLEGE_ID','=',$value];
        }
        if (!empty($listCondition['PROPERTYRIGHT_ID'])) {
            $value = $listCondition['PROPERTYRIGHT_ID'];
            $condition[] = ['PROPERTYRIGHT_ID','=',$value];
        }
        if (!empty($listCondition['POWERTYPE_ID'])) {
            $value = $listCondition['POWERTYPE_ID'];
            $condition[] = ['POWERTYPE_ID','=',$value];
        }
        if (!empty($listCondition['GETTYPE_ID'])) {
            $value = $listCondition['GETTYPE_ID'];
            $condition[] = ['GETTYPE_ID','=',$value];
        }
        if (!empty($listCondition['PURCHASE_ID'])) {
            $value = $listCondition['PURCHASE_ID'];
            $condition[] = ['PURCHASE_ID', '=', $value];
        }
        if (!empty($listCondition['LAND_SIZE_START'])) {
            $value = $listCondition['LAND_SIZE_START'];
            $condition[] = ['LAND_SIZE', '>=', $value];
        }
        if (!empty($listCondition['LAND_SIZE_END'])) {
            $value = $listCondition['LAND_SIZE_END'];
            $condition[] = ['LAND_SIZE', '<=', $value];
        }
        if (!empty($listCondition['LAND_CERT_SIZE_START'])) {
            $value = $listCondition['LAND_CERT_SIZE_START'];
            $condition[] = ['LAND_CERT_SIZE', '>=', $value];
        }
        if (!empty($listCondition['LAND_CERT_SIZE_END'])) {
            $value = $listCondition['LAND_CERT_SIZE_END'];
            $condition[] = ['LAND_CERT_SIZE', '<=', $value];
        }
        if (!empty($listCondition['LAND_FEE_START'])) {
            $value = $listCondition['LAND_FEE_START'];
            $condition[] = ['LAND_FEE', '>=', $value];
        }
        if (!empty($listCondition['LAND_FEE_END'])) {
            $value = $listCondition['LAND_FEE_END'];
            $condition[] = ['LAND_FEE', '<=', $value];
        }

        if (!empty($listCondition['GET_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','>=',$value];
        }
        if (!empty($listCondition['GET_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','<=',$value];
        }
        return $condition;
    }
}